<?php

/*
$values=array('type'=>0,//必须参数
                   'xAxis'=>array('qy'=>array(0=>'1',1=>''),'cjje'=>array(0=>'0-60|60-100|100-150|150-200',1=>'2')),
                   'yAxis'=>array('JZMJ'=>'0-60|60-100|100-150|150-200'), 
                   'vAxis'=>array('CJJE'=>array(0=>'sum,avg',1=>'1')), 
                   'where'=>array('KFS'=>array('values'=>'龙','relation'=>'Included')),
                   'count'=>array('isXSum'=>1,'isYSum'=>1,'YoY'=>array('period'=>'T|P','column'=>'JZMJ_SUM'),'MoM'=>array('period'=>'T|P','column'=>'JZMJ_SUM')),
                   'ranking'=>array('topNum'=>20,'topColumn'=>'JZMJ_SUM_DESC'))
               );
 */

namespace BI\Controller;
/**
 * Description of TransParamController
 *
 * @author caoch
 */
class TransParamController {
    protected $type=0;//统计方式，0（默认）为交叉分析，1为排名分析，2为带同比及环比，3为存量
    protected $xAxis=array();//x维度-数组
    protected $yAxis=array();//y维度-数组
    protected $vAxis=array();//v维度-数组
    protected $where='';//w维度-字符串
    protected $xNums=0;//x维度数量
    protected $yNums=0;//y维度数量
    protected $vNums=0;//v维度数量
    
    protected $count;//统计方式
    protected $ranking;//排名
    protected $levelNums;//表头层级数
    protected $error;
    protected $region='';//区间段数组
    protected $regionColumn='';//区间段参数数组
    
    protected $rulesArr=array();//字段配置表
    private $countArr=array();//统计规则配置表
    private $uomArr=array();//单位配置表
    
    private $datatable='';//数据源表    
    private $decimal=2;//小数位数
    
    protected $headData=array();//表头数组
    protected $footData=array();//合计数组
    protected $sumData=array();//合计数组--仅当同时存在横向和纵向时

    protected $tempTableArr=array();
    protected $DataType;
    protected $condition;


    public function __construct($table,$param){
        if(empty($table)){
            $this->error[]=array('A1'=>'没有配置数据表!');
        }  else {
            //$this->datatable = $table;
            $this->getOriginData($table);
            $this->datatable = $table;
        }

        $this->getAxisConfig($table);
        $this->getCountConfig($table);
        $this->getUomConfig($table);

        if(empty($param)){
            $this->error[]=array('A2'=>'缺少必须的维度参数，至少有一个维度值!');            
        }else{
            $this->decimal=6;
            $this->initAxisParam($param);
        }

        unset($param);
        
    }
    
    /**
     * @解析最初始的数据源SQL：此处不对统计字段进行处理
     * @return string
     */
    private function getOriginData($code){
        //初始化表来源数：以确定SQL是否加表名
        $DataArray = $this->getDataBaseConfig();

        $this->DataType = strtoupper($DataArray[$code]['DataType']);

//        switch ($DataType){
//            case 'TABLE'://表名
//            default :
//                $this->datatable = $DataArray[$code]['DataExp'];
//                break;
//            case 'SQL'://SQL语句
//                $this->datatable = '('.$DataArray[$code]['DataExp'].')'.' as otp';
//                break;
//            case 'STORED'://存储过程
//                $table = 'temp_'.date('Ymdhis');
//                $this->tempTableArr[] = $table;
//
//                $this->datatable = $table; 
//                break;
//        }
        //$this->DataType = $DataType;
    }
    
    private function initStoredWhere($where){
        $res = array();
        foreach($where as $key=>$val){
            $key = strtoupper($key);
            
            switch ($key){
                case 'CJRQ':
                    $res['StartDate'] = empty($val[0])?'2010-01-01':$val[0];
                    $res['EndDate'] = empty($val[1])?date('Y-m-d'):$val[1];
                    break;
                case 'QY':
                    $res['QY'] = implode(',', $val);
                    break;
                case 'ZT':
                    $res['ZT'] = implode(',', $val);
                    break;
                case 'KFS':
                    $res['KFS'] = implode(',', $val);
                    break;
                case 'LPMC':
                    $res['LPMC'] = implode(',', $val);
                    break;
                case 'YT':
                    $res['YT'] = implode(',', $val);
                    break;
                case 'XFYT':
                    $res['XFYT'] = implode(',', $val);
                    break;
                case 'HX':
                    $res['HX'] = implode(',', $val);
                    break;
            }
        }
        return $res;
    }

    /**
     * 方法：初始化XYVT维度参数
     * 返回：给维度变量赋值--数组
     * 参数：initAxis($param['xAxis'],0)，前者为参数数组，后者为处理方式：
     */
    private function initAxisParam($param){
        //1.初始化分析类型
        $this->type=(empty($param['type']))?0:$param['type'];

        //2.初始化X维度
        if(!isset($param['xAxis'])){
            $this->xNums = 0;
        }else{
            $this->xAxis=$this->parseAxis($param['xAxis']);
            $this->xNums = count($this->xAxis);
        }
                
        //3.初始化Y维度
        if(!isset($param['yAxis'])){
            $this->yNums=0;
        }else{
            $this->yAxis=$this->parseAxis($param['yAxis']);
            $this->yNums=count($this->yAxis);
        }
        
        //4.初始化V维度
        if(!isset($param['vAxis'])){
            $this->vNums=0;
        }else{
            $this->vAxis=$this->parseCount($param['vAxis']);
            $this->vNums=count($this->vAxis);
        }
//        //5.初始化W条件
        switch ($this->DataType){
            case 'TABLE':
            case 'SQL':
               if(empty($param['where'])){
                    $w=$this->setPutOffCode();//只排除空值
                    if(!empty($w)){
                        $this->where=' where '.implode(' and ', $w);
                    }else{
                        $this->where='';
                    }            
                }else{
                    $this->where=$this->initWhereVar($param['where']);
                } 
                break;
            case 'STORED':
                $this->where=$this->initStoredWhere($param['where']);
                break;
        }
        $this->condition = $param['where'];

        //6.初始化统计要求参数
        if(empty($param['count'])){
            $this->count='';
        }else{
            $this->count=$param['count'];
        }
    
        //7.初始化排名参数
        if(empty($param['ranking'])){
            $this->ranking['topNum']=10;
        }else{
            $this->ranking=$param['ranking'];
        }

        //8.初始化表头层级数量
        if($this->vNums==1){
            $this->levelNums = ($this->xNums>0)?$this->xNums:1;
        }else{
            $this->levelNums = ($this->yNums>0)?$this->xNums+1:$this->xNums;
        }

        //9,销毁变量
        unset($this->param);
    }
    
    
    /**
     * 获取分析表维度信息
     * 返回数组[若存在缓存则直接读取缓存]
     */
    private function getAxisConfig($code){
        $file=$code.'_Rules';

        $rulesArr=F($file);
        if(empty($rulesArr)){
            $sql="select FieldColumn,FieldName,FieldType,Expressions,FieldPrecision,ContrastSheet,ContrastId,ContrastName,ContrastSort,isnull(b.title,'') as UomName "
                  . " from ".C("DB_PREFIX")."AnalysisRules as a"
                  . " left join ".C("DB_PREFIX")."uom"." as b on b.id=a.uom "
                  . "where DataSheet='".$code."'";
            $dataArr=M()->query($sql);
            foreach($dataArr as $val){
                $key=  strtoupper($val['FieldColumn']);
                $arr['FieldName']=$val['FieldName'];
                $arr['FieldType']=$val['FieldType'];
                $arr['Expressions']=$val['Expressions'];
                $arr['FieldPrecision']=$val['FieldPrecision'];
                $arr['ContrastSheet']=$val['ContrastSheet'];
                $arr['ContrastId']=$val['ContrastId'];
                $arr['ContrastName']=$val['ContrastName'];
                $arr['ContrastSort']=$val['ContrastSort'];
                $arr['UomName']=$val['UomName'];
                
                $rulesArr[$key]=$arr;                
            }
            F($file,$rulesArr);
        }
        
        if(empty($rulesArr)){
            $this->error[]=array('A3'=>'读取基础配置表失败!');
        }else{
            $this->rulesArr = $rulesArr;
        }
    }
    
    /**
     * 方法：分析统计数据的计量单位
     * 返回数组[若存在缓存则直接读取缓存]
     */
    private function getUomConfig($code){
        $file=$code.'_Uom';
        $uomArr=F($file);
        if(empty($uomArr)){
            $sql='select a.id as UomId,b.id as UomGroupId,a.title as UomName,IsMain,Rate '
                 .' from '.C("DB_PREFIX")."uom".' as a '
                 .' inner join '.C("DB_PREFIX")."uomgroup ".' as b on b.id=a.uomgroup ';
            $dataArr=M()->query($sql);
            foreach($dataArr as $val){
                $arr=array();
                $key=strtoupper($val['UomId']);
                $arr['UomGroupId']=$val['UomGroupId'];
                $arr['UomName']=$val['UomName'];
                $arr['IsMain']=$val['IsMain'];
                $arr['Rate']=ceil($val['Rate']);
                
                $uomArr[$key]=$arr;
            }
            F($file,$uomArr);
        }
        if(empty($uomArr)){
            $this->error[]=array('A4'=>'读取计量单位配置表失败!');
        }else{
            $this->uomArr=$uomArr;
        }        
    }
    
    /**
     * 方法：获取统计方法数组
     * 返回：统计方法数组
     */    
    private function getCountConfig($code){
        $file=$code.'_Count';
        $countArr=F($file);
        if(empty($countArr)){
            $sql='select Id,Code,Title '
                 .' from '.C("DB_PREFIX")."count".' as a ';
            $dataArr=M()->query($sql);
            foreach($dataArr as $val){
                $arr=array();
                $key=strtoupper($val['Code']);
                $arr['Id']=$val['Id'];
                $arr['CountName']=$val['Title'];
                
                $countArr[$key]=$arr;
            }
            F($file,$countArr);
        }
        if(empty($countArr)){
            $this->error[]=array('A5'=>'读取统计方式配置表失败!');
        }else{
            $this->countArr=$countArr;
        }  
    }
    
        /**
     * @获得数据源表达式
     * @param type $code
     * @return type
     */
    protected function getDataBaseConfig(){
        $file='bdata/BdataBaseConfig';

        $BdataBaseConfig=F($file);
        if(empty($BdataBaseConfig)){
            $sql="select Code,DataType,DataExp "
                  . " from ".C('DB_PREFIX')."BdataBase as a ";
            $dataArr=  M()->query($sql);
            
            foreach($dataArr as $val){
                $key=$val['Code'];
                $BD['DataType']=$val['DataType'];
                $BD['DataExp']=$val['DataExp'];
                
                $BdataBaseConfig[$key] = $BD;
            }
            
            F($file,$BdataBaseConfig);
        }
        
        return $BdataBaseConfig;
    }
    /**
     * 解析参数中的维度值
     * @access array()
     * @return array()
     */
    private function parseAxis($axis){
        $result = array();
        foreach ($axis as $key=>$val){
            $key = strtoupper($key);
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://对照表
                    $result[]=$key;
                    break;
                case 2://统计值
                    $result[]=$key;
                    $this->regionColumn[$key]=$val;
                    break;
                case 3://日期型
                case 4://文本型
                    $result[]=$key;
                    break;
            }
        }
        return $result;
    }
    

    /**
     * 解析参数中的统计值
     * @access array()：V维度数组
     * @return array()
     */
    private function parseCount($axis){
        $result = array();
        foreach ($axis as $key=>$val){
            $key = strtoupper($key);
            if ($this->rulesArr[$key]['FieldType'] == 2){
                if(!empty($key)&&(!empty($val[0])&&$val[0]!='NULL')&&!empty($val[1])){
                    $cou=  explode(',', $val[0]);
                    foreach ($cou as $count){
                        $result[]=array('k'=>$key,'c'=>strtoupper($count),'u'=>$val[1]);
                    }
                }
            }
        }
        return $result;
    }

    /**
     * @方法：去掉代号为0的数据
     * @参数：维度
     * @返回：数组
     */
    private function setPutOffCode(){
        $where=array();
        $i=0;
        if(!empty($this->xAxis)){
            foreach ($this->xAxis as $key){
                if($this->rulesArr[$key]['FieldType']==1){
                    $where[] = 'isnull('.$key.',0)'.'<>'.'0'.' and '.$key.'<>'.'0';
                    ++$i;
                }
            }
        }
        
        if(!empty($this->yAxis)){
            foreach ($this->yAxis as $key){
                if($this->rulesArr[$key]['FieldType']==1){
                    $where[] = 'isnull('.$key.',0)'.'<>'.'0'.' and '.$key.'<>'.'0';
                    ++$i;
                }
            }
        }
        
        return $where;
    }

    /**
     * 方法：处理条件子句
     * 参数：传入条件数组$where
     * 分支：支持不带关系符的条件，以及带关系符的条件
     * 返回：字符串
     */
    private function initWhereVar($where){
        $arr=array();
        
        foreach($where as $key=>$val){
            $key = strtoupper($key);//统一大写
            
            if(is_array($val)){
                if(isset($val['relation'])){//复杂条件
                    $arr[]=$this->getComplexCodition($key,$val);
                }else{//综合查询
                    if(!$this->isArrayNull($val)){
                        $arr[]=$this->getSimpleCodition($key,$val);
                    }
                }
            }else{//快速条件
                $arr[]=$this->getFastCodition($key,$val);
            }
            
//            if(isset($val['relation'])){//复杂条件
//                $arr[]=$this->getComplexCodition($key,$val);
//            }else{//简单条件
//                if(is_array($val)){//综合查询
//                    if(!$this->isArrayNull($val)){
//                        $arr[]=$this->getSimpleCodition($key,$val);
//                    }
//                }elseif(is_string($val)){//快速条件
//                    $arr[]=$this->getFastCodition($key,$val);
//                }
//            }
        }
        
        $w = $this->setPutOffCode();
        
        if(!empty($arr)){
            if(!empty($w)){
                $wh = array_merge($w,$arr);
                $con = ' where '.implode(' and ', $wh);
            }else{
                $con = ' where '.implode(' and ', $arr);
            }
        }else{
            if(!empty($w)){
                $con = ' where '.implode(' and ', $w);
            }else{
                $con = '';
            }                
        }

        return $con;
    }
    
    /**
     * 判断数组是否为空
     * @access private
     * @param string $key 字段
     * @param array $val 值
     * @return bool true-空 or false-非空
     */
    private function isArrayNull($array){
        $i = 0;
        foreach ($array as $val){
            if(!empty($val)){
                ++$i;
            }
        }
        if($i > 0){
            return false;
        }else{
            return true;
        }
    }
    
    /**
     * 快速条件表达符
     * @参数 string $key：字段值
     * @参数 string $val:值
     * @out:条件子句
     */
    private function getFastCodition($key,$val){
        switch ($this->rulesArr[$key]['FieldType']){
            case 1://对照ID
                $s = $this->rulesArr[$key]['Expressions'].' = '.$val;
                break;
            case 3://如年份
                $s = $this->setDateColumn($this->rulesArr[$key]['Expressions'])." = '".$val."'";
                break;
        }
        
        return $s;
    }
    
    /**
     * 简单条件表达符
     * @input $key：字段值，$val值的数组
     * @out:条件子句
     */
    private function getSimpleCodition($key,$val){
        
        switch ($this->rulesArr[$key]['FieldType']){
            case 1://对照ID
                $values = implode(',',$val);
                $s = $this->rulesArr[$key]['Expressions'].' in ('.$values.')';
                break;
            case 2://数字区间
                $s = $this->setNumberBetween($key,$val);
                break;
            case 3://如年份
                $s = $this->setDateBetween($key,$val);
                break;
            case 4://存在转换表达，如年份
                $s = $this->rulesArr[$key]['Expressions']." like '%".$val."%'";
                break;
        }
        
        return $s;
    }

    /**
     * 解析数字字段表达式--条件
     * @access private
     * @param string $key 字段
     * @param array $val 值
     * @return string 条件子句
     */
    private function setNumberBetween($key,$val){
        $min=  empty($val[0])?0:$val[0];
        $max=  empty($val[1])?0:$val[1];
        
        if($min > 0){//最小值大于0
            if($max > 0){
                $s=$this->setNumberColumn($this->rulesArr[$key]['Expressions'])." between ".$min." and ".$max;
            }else{
                $s=$this->setNumberColumn($this->rulesArr[$key]['Expressions'])." >= ".$min;
            }
        }else{
            if($max > 0){//最大值为0或空
                $s=$this->setNumberColumn($this->rulesArr[$key]['Expressions'])." <= ".$max;
            }else{
                $s=$this->setNumberColumn($this->rulesArr[$key]['Expressions'])." = 0";
            }
        }
        
        return $s;
    }
    
    
    /**
     * 解析日期字段表达式--条件
     * @access private
     * @param string $key 字段
     * @param array $val 值
     * @return string 条件子句
     */
    private function setDateBetween($key,$val){
        $min=  empty($val[0])?0:$val[0];
        $max=  empty($val[1])?0:$val[1];
        
        if($min > 0){//最小值大于0
            if($max > 0){
                $s=$this->setDateColumn($this->rulesArr[$key]['Expressions'])." between '".$min."' and '".$max."'";
            }else{
                $s=$this->setDateColumn($this->rulesArr[$key]['Expressions'])." >= '".$min."'";
            }
        }else{
            if($max > 0){//最大值为0或空
                $s=$this->setDateColumn($this->rulesArr[$key]['Expressions'])." <= '".$max."'";
            }else{
                $s=$this->setDateColumn($this->rulesArr[$key]['Expressions'])." = 0";
            }
        }
        
        return $s;
    }
    
     /**
     * 方法：处理比例字段的字段表达式
     * 参数：表达式，如CJJE/JZMJ
     * 用处：主要用于比例字段，比如：建面均价=成交总价/建面，需要判断建面不为0
     */
    
    private function setNumberColumn($exp){
        if(strpos($exp,'/')){
            $arr=  explode('/', $exp);
            return $arr[1].'>0'.' and '.$exp;
        }else{
            return $exp;
        }        
    }
    
    /**
     * 方法：处理日期型字段值
     * 参数：表达式原字段
     */
    private function setDateColumn($exp){
        $arr=  explode('|', $exp);//Y|CJRQ表示成交日期取年份
        
        switch ($arr[0]){
            case 'Y':
                $res = "YEAR(".$arr[1].")";
                break;
            case 'YM':
                $res = "MONTH(".$arr[1].")";
                break;
            case 'YQ':
                $res = "DATEPART(QQ,".$arr[1].")";
                break;
            case 'YW'://周数
                $res = "DATEPART(WW,".$arr[1].")";
                break;
            case 'HH':
                $res = "DATEPART(WW,".$arr[1].")";
                break;
            case 'D':
            default :
                $res = $arr[1];
                break;
        }
        return $res;
    }
    
    
     /**
     * @方法：给字符追加''号
     * @参数：条件值
     * @返回：带''号的条件值
     */
    private function getAddQuotation($str){
        $array=  explode(',', $str);
        $col=array();
        foreach($array as $val){
            $col[]="'".$val."'";
        }
        $string=  implode($col, ',');
        return $string;
    }
    
    /**
     * @方法：根据复杂条件进行多关联符的条件处理
     * @参数 string $key 字段
     * @参数 array $val 值
     * @return string
     */
    private function getComplexCodition($key,$val){
        switch (strtoupper($val['relation'])){
            case 'EQ':
            case 'EQUAL'://等于
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions'].' = '.$val['values'];
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions'])." = '".$val['values']."'";
                        break;
                }
                break;
            case 'NOTEQUAL'://不等于
            case 'NEQ':
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions'].' <> '.$val['values'];
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions'])." <> '".$val['values']."'";
                        break;
                }
                break;
            case 'LIKE'://模糊查询
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions']." in ("."select ".$this->rulesArr[$key]['ContrastId']." from ".$this->rulesArr[$key]['ContrastSheet']." where ".$this->rulesArr[$key]['ContrastName']." like '%".$val['values']."%')";
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions'])." like '%".$val['values']."%')";
                        break;
                }
                break;
            case 'NOTLIKE'://模糊查询
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions']." in ("."select ".$this->rulesArr[$key]['ContrastId']." from ".$this->rulesArr[$key]['ContrastSheet']." where ".$this->rulesArr[$key]['ContrastName']." not like '%".$val['values']."%')";
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions'])." not like '%".$val['values']."%')";
                        break;
                }
                break;
            case 'INCLUDED'://包含    
            case 'IN':
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions']." in (".$val['values'].")";
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions']).' in ('.$this->getAddQuotation($val['values']).')';
                        break;
                }  
                break;
            case 'NOTINCLUDED'://包含    
            case 'NOTIN':
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1://若为对照表字段
                        $s = $this->rulesArr[$key]['Expressions']." not in (".$val['values'].")";
                        break;
                    case 3://存在转换表达，如年份
                        $s = $this->setDateColumn($this->rulesArr[$key]['Expressions']).' not in ('.$this->getAddQuotation($val['values']).')';
                        break;
                }  
                break;
            case 'BETWEEN'://包含    
            case 'BT':
                $s = $this->setNumberBetween($key,$val['values']);
                break;
        }
        return $s;        
    }   
    
    
    /**
     * @方法：根据键值及相对应的区间段值，解析成CASE语句
     * @参数：传入字段名
     * @参数：区间段数组，0-字符串，1-计量单位
     * @返回：SQL字符串
     * @赋值：区间段数组
     */
    protected function setRegionCaseWhen($key,$reg){
        $i=1;//用于判断首次
        $regArr=  explode('|', $reg[0]);
        $rate=$this->setUomRateFormats($reg[1]);
        $uom = $this->uomArr[$reg[1]]['UomName'];
        
        //$uom=$this->rulesArr[$key]['UomName'];
        $resArr=array();//记录各区间段的ID及TITLE
        
        //判断是否存在比例值
        $column=$this->setNumberExp($this->rulesArr[$key]['Expressions']);
        
        $resString='(case ';

        foreach($regArr as $valArr){
            $aArr=  explode('-', $valArr);
            
            $min=$aArr[0]*$rate;
            $max=$aArr[1]*$rate;
            
            $minName = $aArr[0];
            $maxName = $aArr[1];
                        
            $rName=$minName.'-'.$maxName;
            
            if($i==1){
                if($min>0){//最小值不为0
                    $resString.=' when '.$column.'<='.$min.' then '.$i;
                    $resArr[$i]['id']=$i;
                    $resArr[$i]['code']='0-'.$min;
                    $resArr[$i]['title']='<='.$minName.$uom;

                    $i++;
                    $resString.=' when '.$column.'<='.$max.' and '.$column.'>'.$min.' then '.$i;
                    $resArr[$i]['id']=$i;
                    $resArr[$i]['code']=$min.'-'.$max;
                    $resArr[$i]['title']=$rName.$uom;
                }else{//最小值为0
                    $resString.=' when '.$column.'<='.$max.' then '.$i;
                    $resArr[$i]['id']=$i;
                    $resArr[$i]['code']='0-'.$max;
                    $resArr[$i]['title']='<='.$maxName.$uom;
                }
            }else{
               $resString.=' when '.$column.'<='.$max.' and '.$column.'>'.$min.' then '.$i;
               $resArr[$i]['id']=$i;
               $resArr[$i]['code']=$min.'-'.$max;
               $resArr[$i]['title']=$rName.$uom;
            }                
            $i++;
        }
        //最后追加最大区间段
        $resString.=' when '.$column.'>'.$max.' then '.$i;
        $resArr[$i]['id']=$i;
        $resArr[$i]['code']=$max.'-0';
        $resArr[$i]['title']='>'.$maxName.$uom;

        $resString.=' end) as '.$key.'_A';//追加_A的字段
        
        $this->region[$key] = $resArr;//用于写临时文件

        return $resString;
    }
    
    
     /**
     * 方法：处理比例字段的字段表达式
     * 参数：表达式，如CJJE/JZMJ
     * 用处：遇到比例字段时，字段表达式
     */
    
    private function setNumberExp($exp){
        if(strpos($exp,'/')){
            $arr=  explode('/', $exp);
            return '(case when '.$arr[1].'>0'.' then '.$exp.' else 0 end)';
        }else{
            return $exp;
        }        
    }
    
    /**
     * @方法：处理一些日期常量的表达式,主要用于解析时间相关的表达式
     * @参数：常量值
     */
    private function setDateExp($exp){
        $arr=  explode('|', $exp);//Y|CJRQ表示成交日期取年份
        
        switch ($arr[0]){
            case 'Y'://年份
                $res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'年'";
                break;
            case 'YM'://年月
                //$res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'.'+CONVERT(nvarchar(2),DATEPART(mm,".$arr[1]."))";
                $res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'年'+RIGHT('00'+CAST(MONTH(".$arr[1].") AS VARCHAR(2)),2)+'月'";
                break;
            case 'YW'://年周
                //$res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'年第'+CONVERT(nvarchar(2),DATEPART(WW,".$arr[1]."))+'周('";
                $res = "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr[1].")),0),120)";//周一作为周的第一天,主要注意周日要减一天
                $res .= "+'至'+";
                $res .= "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr[1].")),6),120)";//周日作为周的最后一天
                
                //$res .= "+')'+";
                break;
            case 'YQ'://年季
                $res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'年第'+CONVERT(nvarchar(2),DATEPART(QQ,".$arr[1]."))+'季度'";
                break;
            case 'YB'://半年
                $res = "CONVERT(nvarchar(4),YEAR(".$arr[1]."))+'年'+(case when MONTH(".$arr[1].")<=6 then '上半年' else '下半年' end)";
                break;
            case 'HH'://小时24小时制
                $res = "CONVERT(nvarchar(2),datepart(hh,".$arr[1]."))";
                break;
            case 'D'://日
            default :
                $res = "CONVERT(nvarchar(10),".$arr[1].")";
                break;
        }
        return $res;
    }

     /**
     * @解析维度最原始的取数表达式
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseOriginSelectColumn($axis){
        foreach ($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                    $resArr[]=$this->rulesArr[$key]['Expressions'];
                    break;
                case 2://统计
                    //生成带_A的SQL串
                    $resArr[]=$this->setRegionCaseWhen($key,$this->regionColumn[$key]);
                    break;
                case 3:
                    $resArr[]=$this->setDateExp($this->rulesArr[$key]['Expressions']).' as '.$key;
                    break;
                case 4:
                    $resArr[]=$this->rulesArr[$key]['Expressions'];
                    break;
            }
        }
        return implode(',', $resArr);
    }
    
    /**
     * @解析维度最原始的取数表达式，特殊计算
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseOriginSelectColumnExp($id,$axis){
        foreach ($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                    if($key=='KFS'){
                        $resArr[]=$id.' as '.$key;
                    }else{
                        $resArr[]=$this->rulesArr[$key]['Expressions'];
                    }                    
                    break;
                case 2://统计
                    //生成带_A的SQL串
                    $resArr[]=$this->setRegionCaseWhen($key,$this->regionColumn[$key]);
                    break;
                case 3:
                    $resArr[]=$this->setDateExp($this->rulesArr[$key]['Expressions']).' as '.$key;
                    break;
                case 4:
                    $resArr[]=$this->rulesArr[$key]['Expressions'];
                    break;
            }
        }
        return implode(',', $resArr);
    }
    
    /*
     * 方法：根据字段名、单位、统计规则拼凑统计字段格式
     */
    protected function setCountColumnFormats($key,$count,$uom){
        return $key.'_'.$count;
    }
    
    /**
     * 方法：根据选择的单位编号，计算出数据表达式，如万平方=平方/10000:用于原字段的处理
     * 参数：$countField字段名，$uom当前单位ID，自动识别同组单位ID以及与主单位之间的换算率
     * 返回字符串
     */
    private function setColumnUomFormats($key,$uom){
        //1.主计量单位
        if($this->uomArr[$uom]['IsMain']==1){
            $result = $key;
        }else{//2.非主计量单位
            $result = $key.'/'.$this->uomArr[$uom]['Rate'].' as '.$key;
        }
        
        return $result;
    }
    
        /**
     * 方法：根据选择的单位编号，计算出数据表达式，主要用于区间段时按单位计算
     * 参数：$countField字段名，$uom当前单位ID，自动识别同组单位ID以及与主单位之间的换算率
     * 返回字符串
     */
    private function setUomRateFormats($uom){
        //1.主计量单位
        if($this->uomArr[$uom]['IsMain']==1){
            $rate = 1;
        }else{//2.非主计量单位
            $rate = $this->uomArr[$uom]['Rate'];
        }
        
        return $rate;
    }
    
    /**
     * @方法：根据选择的单位编号，计算出数据表达式，如万平方=平方/10000:用于原字段的处理
     * @用法：用于直接统计数据
     * @参数：$countField字段名，$uom当前单位ID，自动识别同组单位ID以及与主单位之间的换算率
     * @返回字符串
     */
    private function setSourceColumnUomFormats($key,$count,$uom){
        //1.主计量单位
        if($this->uomArr[$uom]['IsMain']==1){
            $result = $key;                
        }else{//2.非主计量单位
            $result = $key.'/'.$this->uomArr[$uom]['Rate'];
        }
        
        return $result;
    }
    
    /**
     * @解析:维度最原始的取数表达式；
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseOriginCountColumn($axis){

        foreach ($axis as $val){
            $e = $this->rulesArr[$val['k']]['Expressions'];
            if(strpos($e,'/')){//存在比例关系
                $varr=  explode('/', $e);
                $resArr[] = $varr[0].' as '.$varr[0].'_0';
                $resArr[] = $varr[1].' as '.$varr[1].'_0';
            }elseif($e==1){
                $resArr[] = '1 as '.$val['k'];
            }else{
                $resArr[] = $this->setColumnUomFormats($val['k'],$val['u']);
            }
        }

        return implode(',', array_unique($resArr));
    }
    
        /**
     * @解析:维度最原始的取数表达式；
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseStockOriginCountColumn($axis){

        foreach ($axis as $val){
            $e = $this->rulesArr[$val['k']]['Expressions'];
            if(strpos($e,'/')){//存在比例关系
                $varr=  explode('/', $e);
                $resArr[] = $varr[0].' as '.$varr[0].'_0';
                $resArr[] = $varr[1].' as '.$varr[1].'_0';
            }elseif(strpos($e,'+')){
                $varr=  explode('+', $e);
                $resArr[] = $varr[0].' as '.$varr[0].'_0';
                $varr1=  explode('-', $varr[1]);
                $resArr[] = $varr1[0].' as '.$varr1[0].'_0';
                $resArr[] = $varr1[1].' as '.$varr1[1].'_0';
            }elseif($e==1){
                $resArr[] = '1 as '.$val['k'];
            }else{
                $resArr[] = $this->setColumnUomFormats($val['k'],$val['u']);
            }
        }

        return implode(',', array_unique($resArr));
    }
    
    /**
     * @解析:维度统计表达式
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseCountColumn($axis){

        foreach($axis as $val){
            if(strpos($this->rulesArr[$val['k']]['Expressions'],'/')){
                $arr =  explode('/', $this->rulesArr[$val['k']]['Expressions']);
                $res[] = $arr[0];
                $res[] = $arr[1];
            }elseif($this->rulesArr[$val['k']]['Expressions'] == 1){
                $res[] = '1 as '.$val['k'];
            }else{
                $res[] = $val['k'];
            }
        }

        return implode(',', array_unique($res));
    }
 
    /**
     * @方法：统一数字型数据读取时的格式及小数位数:只有当只有XV无Y时需要统一格式
     * @参数：统计计算字段表达式
     * @返回：SQL表达式
     */
    private function setNumericFormat($key,$exp){       
//        if($this->xNums>0 && $this->vNums>0 && $this->yNums==0){
//            return 'cast(round('.$exp.','.$this->decimal.') as numeric(18,'.$this->decimal.'))';
//        }else{
//            return $exp;
//        }
        //等长的数据格式，速度更快
        return 'cast(round('.$exp.','.$this->decimal.') as numeric(18,'.$this->decimal.'))';
    }

        /**
     * @方法：处理RATE占比字段的计算表达式
     * @参数：统计维度
     * @返回：SQL表达式
     */
    private function setRateCountColumn($val){
        $molecule='sum'.'('.$val['k'].'_'.'0'.')';  
        $denominator='sum'.'('.$val['k'].')'; 
        
        //获取字段
        if($this->levelNums>1){
            $where = array();
            foreach ($this->xAxis as $key){
                switch ($this->rulesArr[$key]['FieldType']){
                    case 1:
                        $where[] = $key.'='.$key;
                        break;
                }
            }
            if(!empty($where)){
                $w = $this->where;//$w = $this->where.' and '.implode(' and ', $where);
            }else{
                $w = $this->where;
            }
            
            $total = '(select '.$denominator.' from '.$this->datatable.$w.')';
        }else{//仅一层表头时
            $total = '(select '.$denominator.' from '.$this->datatable.' '.$this->where.')';
        }
        
        $exp = '100*'.$molecule.'/'.$total;
        
        return $this->setNumericFormat('RATE',$exp);
    }

    /**
     * @解析:统计字段的统计表达式---非直接统计
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function getCountColumn($axis){

        foreach($axis as $val){
            if(strpos($this->rulesArr[$val['k']]['Expressions'],'/')){
                $arr =  explode('/', $this->rulesArr[$val['k']]['Expressions']);
                $column = '(case when '.'sum('.$arr[1].'_0'.')>0'.' then '.'sum('.$arr[0].'_0'.')'.'/'.'sum('.$arr[1].'_0'.')'.' else 0 end)';
                $exp = $this->setNumericFormat($val['k'],$column);
                
                $res[] = $exp.' as '.$this->setCountColumnFormats($val['k'],$val['c'],$val['u']);
            }elseif($this->rulesArr[$val['k']]['Expressions'] == 1){
                $exp = $this->setNumericFormat($val['k'],$val['c'].'('.$val['k'].')');
                $res[] = $exp.' as '.$this->setCountColumnFormats($val['k'],$val['c'],$val['u']);
            }else{
                if($val['c']=='RATE'){
                    //计算百分比        
                    $res[] = $this->setRateCountColumn($val).' as '.$val['k'].'_'.$val['c'];
                }else{
                    $exp = $this->setNumericFormat($val['k'],$val['c'].'('.$val['k'].')');
                    $res[] = $exp.' as '.$this->setCountColumnFormats($val['k'],$val['c'],$val['u']);
                }                
            }
        }

        return implode(',', $res);
    }
    
    
    
    /**
     * @解析:统计字段的统计表达式--直接统计
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function getNotCountColumn($axis){

        foreach($axis as $val){
            if(strpos($this->rulesArr[$val['k']]['Expressions'],'/')){
                $arr =  explode('/', $this->rulesArr[$val['k']]['Expressions']);
                $column = '(case when '.'sum('.$arr[1].')>0'.' then '.'sum('.$arr[0].')'.'/'.'sum('.$arr[1].')'.' else 0 end)';
                
                $exp = $this->setNumericFormat($val['k'],$column);
                $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
            }elseif($this->rulesArr[$val['k']]['Expressions'] == 1){
                //$column = $val['c'].'('.$this->setSourceColumnUomFormats($val['k'],$val['c'],$val['u']).')';
                $column = 'sum(1)';
                $exp = $this->setNumericFormat($val['k'],$column);
                
                $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
            }else{
                if($val['c']=='RATE'){
                    //计算百分比        
                    $res[] = $this->setRateCountColumn($val).' as '.$val['k'].'_'.$val['c'];
                }else{
                    $column = $val['c'].'('.$this->setSourceColumnUomFormats($val['k'],$val['c'],$val['u']).')';
                    $exp = $this->setNumericFormat($val['k'],$column);
                
                    $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
                }                
            }
        }

        return implode(',', $res);
    }
    
    /**
     * @解析:统计字段Stock式--直接统计
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function getStockCountColumn($axis){

        foreach($axis as $val){
            if(strpos($this->rulesArr[$val['k']]['Expressions'],'/')){
                $arr =  explode('/', $this->rulesArr[$val['k']]['Expressions']);
                $column = '(case when '.'sum('.$arr[1].')>0'.' then '.'sum('.$arr[0].')'.'/'.'sum('.$arr[1].')'.' else 0 end)';
                
                $exp = $this->setNumericFormat($val['k'],$column);
                $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
            }elseif(strpos($this->rulesArr[$val['k']]['Expressions'],'+')){
                if($val['k'] == 'QMJM'){
                    $column = 'sum(QCJM)+sum(GYJM)-sum(CJJM)';
                }elseif($val['k'] == 'QMTM'){
                    $column = 'sum(QCTM)+sum(GYTM)-sum(CJTM)';
                }elseif($val['k'] == 'QMTS'){
                    $column = 'sum(QCTS)+sum(GYTS)-sum(CJTS)';
                }
                $exp = $this->setNumericFormat($val['k'],$column);
                $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
            }elseif($this->rulesArr[$val['k']]['Expressions'] == 1){
                //$column = $val['c'].'('.$this->setSourceColumnUomFormats($val['k'],$val['c'],$val['u']).')';
                $column = 'sum(1)';
                $exp = $this->setNumericFormat($val['k'],$column);
                
                $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
            }else{
                if($val['c']=='RATE'){
                    //计算百分比        
                    $res[] = $this->setRateCountColumn($val).' as '.$val['k'].'_'.$val['c'];
                }else{
                    $column = $val['c'].'('.$this->setSourceColumnUomFormats($val['k'],$val['c'],$val['u']).')';
                    $exp = $this->setNumericFormat($val['k'],$column);
                
                    $res[] = $exp.' as '.$val['k'].'_'.$val['c'];
                }                
            }
        }

        return implode(',', $res);
    }
    
    
    /**
     * @解析:根据统计维度数组，解析统计字段--原表达式
     * @参数：统计数组
     * @返回：统计字符串
     */
    private function parseScrapeSelectColumn($axis){
        $nums=count($axis);//维度个数
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                    if($nums>1){
                        $resArr[]='cast('.$key.' as varchar(20))';
                    }else{
                        $resArr[]=$key;
                    }                    
                    break;
                case 2://统计
                    if($nums>1){
                        //$resArr[]='cast('.$key.'_A'.' as varchar(20))';
                        //$resArr[]='cast('.$key.' as varchar(20))';
                        $resArr[]=$this->rulesArr[$key]['ContrastSheet'].'.'.$this->rulesArr[$key]['ContrastName'];
                    }else{
                        $resArr[]=$key;
                    }
                    break;
                case 3://时间值
                case 4:
                    $resArr[]=$key;
                    break;
            }
        }
        return implode("+'@'+", $resArr);
    }
    
    /**
     * @解析:根据统计维度数组，解析统计字段--原表达式:特殊计划
     * @参数：统计数组
     * @返回：统计字符串
     */
    private function parseScrapeSelectColumnExp($id,$axis){
        $nums=count($axis);//维度个数
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                    if($nums>1){
                        if($key=='KFS'){
                            $resArr[]='cast('.$id.' as varchar(20))';
                        }else{
                            $resArr[]='cast('.$key.' as varchar(20))';
                        }                        
                    }else{
                        if($key=='KFS'){
                            $resArr[]=$id;
                        }else{
                            $resArr[]=$key;
                        }
                    }                    
                    break;
                case 2://统计
                    if($nums>1){
                        //$resArr[]='cast('.$key.'_A'.' as varchar(20))';
                        //$resArr[]='cast('.$key.' as varchar(20))';
                        $resArr[]=$this->rulesArr[$key]['ContrastSheet'].'.'.$this->rulesArr[$key]['ContrastName'];
                    }else{
                        $resArr[]=$key;
                    }
                    break;
                case 3://时间值
                case 4:
                    $resArr[]=$key;
                    break;
            }
        }
        return implode("+'@'+", $resArr);
    }
    
        /**
     * @解析:根据统计维度数组，解析统计字段--转换表达式
     * @参数：统计数组
     * @返回：统计字符串
     */
    private function parseScrapeChangeColumn($axis){
        $nums=count($axis);//维度个数
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                    if($nums>1){
                        $resArr[]='cast('.$key.' as varchar(20))';
                    }else{
                        $resArr[]=$key;
                    }                    
                    break;
                case 2://统计
                    if($nums>1){
                        $resArr[]='cast('.$key.'_A'.' as varchar(20))';
                    }else{
                        $resArr[]=$key.'_A';
                    }
                    break;
                case 3://时间值
                case 4:
                    $resArr[]=$key;
                    break;
            }
        }
        return implode("+'@'+", $resArr);
    }
    
    /**
     * @解析:解析group by 字符串
     * @参数：维度数组
     * @返回：表达字符串
     */
    private function parseScrapeGroupBy($axis){
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 1://参照
                case 3:
                case 4:
                    $resArr[]=$key;                 
                    break;
                case 2://统计
                    $resArr[]=$key.'_A';
                    break;
            }
        }
        return implode(",", $resArr);
    }
    
    /**
     * @方法：判断维度中是否含有区间段或日期等需要转换的字段
     * @参数：维度数组
     * @返回：bool，存在需要转换的字段，则返回true
     */
    private function isChangeColumn($axis){
        $i=0;
        foreach ($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 2:
                case 3:
                    ++$i;
                    break;
                case 1:
                case 4:
                default :
                    break;
            }
        }
        
        if($i>0){
            return true;
        }else{
            return false;
        }
    }

     /**
     * @方法：将维度字段进行处理，特别是将带_A转换成正常字段
     * @参数：维度数组
     * @返回：关联数组
     */
    private function setChangColumn($axis){
        $select=array();
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 2:
                    $select[] = $key.'_A'.' as '.$key;
                    break;
                case 1:
                case 3:
                case 4:
                default :
                    $select[] = $key;
                    break;
            }
        }
        
        return implode(',', $select);
    }
      
    /**
     * @方法：将维度字段进行处理，特别是将带_A转换成正常字段
     * @参数：维度数组
     * @返回：关联数组
     */
    private function setChangColumnExp($id,$axis){
        $select=array();
        
        foreach($axis as $key){
            switch ($this->rulesArr[$key]['FieldType']){
                case 2:
                    $select[] = $key.'_A'.' as '.$key;
                    break;
                case 1:
                case 3:
                case 4:
                default :
                    if($key=='KFS'){
                        $select[] = $id;
                    }else{
                        $select[] = $key;
                    }
                    
                    break;
            }
        }
        
        return implode(',', $select);
    }

    
    /**
     * @方法：更新配置表指定键名的值:只对变量临时修改
     * @参数：键值，修改值数组
     * @用途：当存X维度在区间值时，动态追加临时表及配置信息
     */
    private function setRulesValues($key,$valArr){        
        if(is_array($valArr)){
            foreach($valArr as $k=>$val){
                $this->rulesArr[$key][$k]=$val;
            }
        }
    }
    
    /**
     * @方法：根据区间值，生成临时表，并且将临时表名写入到临时表属性中，方便事件完全结束后删除
     * @参数：参数名及区间数组
     * @生成临时表
     */
    private function createRegionTempTable($region){
        foreach ($region as $key=>$arr){
            $temp='Re_'.$key.date('Ymdhis');

            $sql='CREATE TABLE '.$temp.'('
                .'[id] [bigint] NOT NULL,'
                .'[code] [nvarchar](100) NULL,'
                .'[title] [nvarchar](255) NULL,'
                .'CONSTRAINT [PK_'.$temp.'] PRIMARY KEY CLUSTERED ' 
                .'('
                .'[id] ASC'
                .')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
                .') ON [PRIMARY]';

            if(M()->execute($sql)){//如果执行创建成功
                $sqlStr='insert into '.$temp.'(id,code,title) ';
                $i=0;
                foreach($arr as $val){
                    if($i==0){
                        $sqlStr.=" select ".$val['id'].",'".$val['code']."'".",'".$val['title']."'";
                    }else{
                        $sqlStr.="union all select ".$val['id'].",'".$val['code']."'".",'".$val['title']."'";
                    }
                    $i++;
                }
                M()->execute($sqlStr);

                //1.将创建的临时表名写入数组
                $this->tempTableArr[]=$temp;

                //2.更新配置表
                $valArr=array('ContrastSheet'=>$temp,'ContrastId'=>'id','ContrastName'=>'title','ContrastSort'=>'id');
                $this->setRulesValues($key,$valArr);
            }
        }
    }


    /**
     * @方法：处理表头数据的中文结果，带@
     * @参数：维度数组
     * @返回：关联数组
     */
    private function setHeadInner($axis){
        //1.创建区间段的临时表
        $this->createRegionTempTable($this->region);
        unset($this->region);
        
        //2.处理对照表信息
        $i=0;
        foreach($axis as $key){
            $tb='htb'.$i;
            
            switch ($this->rulesArr[$key]['FieldType']){
                case 1:
                    $ID[] = 'cast('.$tb.'.'.$this->rulesArr[$key]['ContrastId'].' as varchar(30))';
                    $select[] = $tb.'.'.$this->rulesArr[$key]['ContrastName']; 
                    $inner[] = 'inner join '.$this->rulesArr[$key]['ContrastSheet'].' as '.$tb.' on '.$tb.'.'.$this->rulesArr[$key]['ContrastId'].'='.$key;
                    $order[] = $tb.'.'.$this->rulesArr[$key]['ContrastSort'];
                    break;
                case 2:
                    $ID[] = 'cast('.$tb.'.'.'code'.' as varchar(30))';
                    $select[] = $tb.'.'.$this->rulesArr[$key]['ContrastName']; 
                    $inner[] = 'inner join '.$this->rulesArr[$key]['ContrastSheet'].' as '.$tb.' on '.$tb.'.'.$this->rulesArr[$key]['ContrastId'].'='.$key;
                    $order[] = $tb.'.'.$this->rulesArr[$key]['ContrastSort'];
                    break;
                case 3:
                case 4:
                default :
                    $ID[] = $key;
                    $select[]=$key;
                    $inner[]='';
                    $order[]=$key;
                    break;
            }
            ++$i;
        }
        
        return array('id'=>$ID,'select'=>$select,'inner'=>$inner,'order'=>$order);
    }
    
    /**
     * @方法：处理表体数据的中文结果，带@
     * @参数：维度数组
     * @返回：关联数组
     */
    private function setDataInner($axis){
        //1.将转换字段写入临时表，否则无法获取对照中文
        if($this->isChangeColumn($this->yAxis)){
            $region=array();
            foreach ($this->region as $key=>$val){
                if(in_array($key, $this->yAxis)){
                    $region[$key]=$val;
                }
            }
            $this->createRegionTempTable($region);
            unset($this->region);
        }        

        //2.根据设置关联对照的中文名
        $i=0;
        foreach($axis as $key){
            $tb='tb'.$i;
            switch ($this->rulesArr[$key]['FieldType']){
                case 1:
                    $id[] = 'cast('.$key.' as varchar(30))';
                    $select[] = $tb.'.'.$this->rulesArr[$key]['ContrastName'].' as '.$key; 
                    $inner[] = 'inner join '.$this->rulesArr[$key]['ContrastSheet'].' as '.$tb.' on '.$tb.'.'.$this->rulesArr[$key]['ContrastId'].'='.$key;
                    $order[] = $tb.'.'.$this->rulesArr[$key]['ContrastSort'];
                    break;
                case 2:
                    $id[] = 'cast('.$tb.'.'.'code'.' as varchar(30))';//'cast('.$key.' as varchar(30))';
                    $select[] = $tb.'.'.$this->rulesArr[$key]['ContrastName'].' as '.$key; 
                    $inner[] = 'inner join '.$this->rulesArr[$key]['ContrastSheet'].' as '.$tb.' on '.$tb.'.'.$this->rulesArr[$key]['ContrastId'].'='.$key;
                    $order[] = $tb.'.'.$this->rulesArr[$key]['ContrastSort'];
                    break;
                case 3:
                case 4:
                default :
                    $id[]=$key;
                    $select[]=$key;
                    $inner[]='';
                    $order[]=$key;
                    break;
            }
            ++$i;
        }
   
        return array('select'=>$select,'inner'=>$inner,'order'=>$order,'id'=>$id);
    }
    
    /**
     * @方法：处理Stock中文结果，带@
     * @参数：维度数组
     * @返回：关联数组
     */
    private function setStockInner($axis){
        //1.将转换字段写入临时表，否则无法获取对照中文
        if($this->isChangeColumn($this->yAxis)){
            $region=array();
            foreach ($this->region as $key=>$val){
                if(in_array($key, $this->yAxis)){
                    $region[$key]=$val;
                }
            }
            $this->createRegionTempTable($region);
            unset($this->region);
        }        

        //2.根据设置关联对照的中文名
        $i=0;
        foreach($axis as $key){
            $tb='tb'.$i;
            switch ($this->rulesArr[$key]['FieldType']){
                case 1:
                    $id[] = 'cast('.$key.' as varchar(30))';
                    $select[] = $tb.'.'.$this->rulesArr[$key]['ContrastName'].' as '.$key; 
                    $inner[] = 'inner join '.$this->rulesArr[$key]['ContrastSheet'].' as '.$tb.' on '.$tb.'.'.$this->rulesArr[$key]['ContrastId'].'='.$key;
                    $order[] = $tb.'.'.$this->rulesArr[$key]['ContrastSort'];
                    break;
                case 3:
                default :
                    $id[]=$key;                    
                    $inner[]='';
                    $select[]=$key;
                    $order[]=$key;
                    break;
            }
            ++$i;
        }
   
        return array('select'=>$select,'inner'=>$inner,'order'=>$order,'id'=>$id);
    }
    
    /**
     * @方法：处理带有对照表的语句[当表头维度存在对照时需要执行此方法]
     * @参数：维度值
     * @返回：SQL
     */
    private function setContrastAsix($r){
        //去重数据
        $rsql = 'select '
                .$this->setChangColumn($this->xAxis)
                .' from '
                .'('.$r.') as t'
                .' group by '
                .$this->parseScrapeGroupBy($this->xAxis);

        $Inner = $this->setHeadInner($this->xAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode("+'@'+", $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);

        //加中文识别
        $sql = 'select '
                .$id.' as id,'
                .$this->parseScrapeSelectColumn($this->xAxis)
                .' as xAxis'
                .','
                .$select
                .' as xName'
                .' from '
                .'('.$rsql.') as tt '
                .$inner
                .' order by '.$order;
        
        return $sql;
    }
    
    /**
     * @方法：处理带有对照表的语句[当表头维度存在对照时需要执行此方法]---需要计算合计值时使用
     * @参数：维度值
     * @返回：SQL
     */
    private function setContrastCountAsix($r){
        //去重数据
        $rsql = 'select '
                .$this->setChangColumn($this->xAxis)
                .','
                .$this->getCountColumn($this->vAxis)
                .' from '
                .'('.$r.') as t'
                .' group by '
                .$this->parseScrapeGroupBy($this->xAxis);

        $Inner = $this->setHeadInner($this->xAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode("+'@'+", $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);
        
        foreach ($this->vAxis as $val){
            $vAxis[] = $this->setCountColumnFormats($val['k'], $val['c'], $val['u']);
        }    
        $v = implode(',', $vAxis);

        //加中文识别
        $sql = 'select '
                .$id.' as id,'
                .$this->parseScrapeSelectColumn($this->xAxis)
                .' as xAxis'
                .','
                .$select
                .' as xName'
                .','
                .$v
                .' from '
                .'('.$rsql.') as tt '
                .$inner
                .' order by '.$order;
        
        return $sql;
    }

    /**
     * @方法：不需求计算FOOT合计或占比时，使用此方法取表头数据
     * @参数：V维度
     * @返回：SQL语句
     */
    private function setNoFootOrRateSql(){
        
        //1.解析数据源SQL
        $r = 'select '
            .$this->parseOriginSelectColumn($this->xAxis)
            .' from '
            .$this->datatable
            .$this->where;
            
        if($this->isChangeColumn($this->xAxis)){//是否存在转换（区间，日期等）
            //2.生成表头数据源
            if($this->isContrastAxis()){//需要对照表-区间
                $sql = $this->setContrastAsix($r);
            }else{//无需对照表--日期
                $sql = 'select '
                        .$this->parseScrapeSelectColumn($this->xAxis)
                        .' as xAxis'
                        .' from '
                        .'('.$r.') as t'
                        .' group by '
                        .$this->parseScrapeGroupBy($this->xAxis)
                        .' order by '
                        .$this->parseScrapeGroupBy($this->xAxis);
            }
        }else{//不存在需要转换的区间段
            if($this->isContrastAxis()){//需要对照表                
                $sql = $this->setContrastAsix($r);
            }else{
                $sql = 'select '
                    .$this->parseScrapeSelectColumn($this->xAxis)
                    .' as xAxis'
                    .' from '
                    .$this->datatable
                    .$this->where
                    .' group by '
                    .$this->parseScrapeGroupBy($this->xAxis)
                    .' order by '
                    .$this->parseScrapeGroupBy($this->xAxis);
            }            
        }

        return $sql; 
    }
    
    /**
     * @方法：需求计算FOOT合计或占比时，使用此方法取表头数据
     * @参数：V维度
     * @返回：SQL语句
     */
    private function setFootOrRateSql(){
        //1.解析数据源SQL
        $r = 'select '
            .$this->parseOriginSelectColumn($this->xAxis)
            .','
            .$this->parseOriginCountColumn($this->vAxis)
            .' from '
            .$this->datatable
            .$this->where;
            
        if($this->isChangeColumn($this->xAxis)){//是否存在转换（区间，日期等）
            //2.生成表头数据源
            if($this->isContrastAxis()){//需要对照表
                $sql = $this->setContrastCountAsix($r);
            }else{//无需对照表
                $sql = 'select '
                        .$this->parseScrapeSelectColumn($this->xAxis)
                        .' as xAxis'
                        .','
                        .$this->getCountColumn($this->vAxis)
                        .' from '
                        .'('.$r.') as t'
                        .' group by '
                        .$this->parseScrapeGroupBy($this->xAxis)
                        .' order by '
                        .$this->parseScrapeGroupBy($this->xAxis);;
            }
        }else{//不存在需要转换的区间段
            if($this->isContrastAxis()){//需要对照表                
                $sql = $this->setContrastCountAsix($r);
            }else{
                $sql = 'select '
                    .$this->parseScrapeSelectColumn($this->xAxis)
                    .' as xAxis'
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$this->where
                    .' group by '
                    .$this->parseScrapeGroupBy($this->xAxis)
                    .' order by '
                    .$this->parseScrapeGroupBy($this->xAxis);;
            }            
        }
   
        return $sql; 
    }
    
    /**
     * @方法：需求计算FOOT合计或占比时，使用此方法取表头数据洽 计值---仅用于需要横向合计时
     * @参数：V维度
     * @返回：SQL语句
     */
    private function setFootOrRateXSumSql(){
        //1.解析数据源SQL
        $r = 'select '
            .$this->parseOriginCountColumn($this->vAxis)
            .' from '
            .$this->datatable
            .$this->where;
        
        $sql = 'select '
            .$this->getCountColumn($this->vAxis)
            .' from '
            .'('.$r.') as t';

        return $sql; 
    }

    /**
     * @方法：判断是否需要合计FOOT或占比计算
     * @返回：true表示需要
     */
    private function isFootOrRate(){
        if($this->count['isYSum'] == 1){
            return true;
        }else{
            if($this->isRateAxis()){
                return true;
            }else{
                return false;
            }
        }
    }

    /**
     * @方法：获取表头数据源
     * @用途：主要是处理横维度的读取方式，多个横向维度时，需要拼凑成带@格式
     * @返回：提取表头数据的SQL
     * @access protected
     */
    private function getHeadSources(){
        
        if($this->isFootOrRate()){//是否需要FOOT//是否需要占比计算
            $sql = $this->setFootOrRateSql();
            if($this->count['isXSum']==1){
                $this->sumData = M()->query($this->setFootOrRateXSumSql());
            }
        }else{
            $sql = $this->setNoFootOrRateSql();         
        }

        return $sql;        
    }
    
    /**
     * @方法：获取分析数据源
     * @用途：读取原始数据
     * @返回：提取数据源的SQL
     * @access protected
     */
    private function getDataSources(){
        if($this->xNums > 0){
            if($this->yNums>0){
                $sql = $this->setXYVAxisSql();
            }else{
                $sql = $this->setXVAxisSql();
            }
        }else{
            if($this->yNums>0){
                $sql = $this->setYVAxisSql();
            }else{
                $sql = '';
            }
        }

        return $sql;
    }
    
    /**
     * @方法：获取分析数据源----排行榜数据源，单个排名依据时
     * @用途：读取原始数据
     * @返回：提取数据源的SQL
     * @access protected
     */
    private function getTopSingleDataSources($topNum,$orderBy){
        $res = $this->setTopSingleAxisSql();
        
        //转换中文 
        $Inner = $this->setDataInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        
        $count = implode(',',array_keys($this->setCountHeader()));
        
        $sql = 'select top '
                .$topNum
                .' ROW_NUMBER() OVER(ORDER BY '.$orderBy.') as rank,'
                .$id.' as id,'
                .$select
                .','
                . $count
                .' from('
                .$res
                .') as tt '
                .$inner
                .' order by '.$orderBy;
        
        return $sql;
    }
    
    /**
     * @方法：获取分析数据源----排行榜数据源，多个排名依据时
     * @用途：读取原始数据
     * @返回：提取数据源的SQL
     * @access protected
     */
    private function getTopMultiDataSources($topNum,$orderBy){
        //数据源
        $Sources = $this->setTopMultiAxisSql();
        
        $col=array();
        $i=1;
        foreach ($this->yAxis as $key){
            if($i < $this->yNums){
               $col[]='t.'.$key; 
            }  
            ++$i;
        }
        
        $partition = implode(',', $col);
        
        $res = 'select t.*,'
                .'dense_rank() over (partition by '.$partition.' order by '.$orderBy.' ) as seq_num '
                .' from ('
                .$Sources.') as t';
        
        //转换中文 
        $Inner = $this->setDataInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        
        $count = implode(',',array_keys($this->setCountHeader()));
        
        $sql = 'select '
                .$id.' as id,'
                .$select
                .','
                . $count
                .' from('
                .$res
                .') as tt '
                .$inner
                .' where seq_num <= '
                .$topNum
                .' order by '.$id;

        return $sql;
    }
    /**
     * @方法：判断是否需要进行特殊计算
     */
    private function setIsExceptionalCount(){
        //判断是否带在开发商信息
        $i=0;
        
        if($this->yNums>0){
            foreach ($this->yAxis as $key){
                if($key=='KFS'){
                    ++$i;
                }
            }
        }
        
        if($i>0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * @方法：特殊取数，用于计算特殊求和
     * @参数：是否计算
     * @返回：SQL字符串
     */
    private function setExceptionalSql($type){
        if($this->setIsExceptionalCount()){//需要特殊计算
            //处理香港置地
            if(empty($this->where)){
                $where=' where (kfs=895 or lpmc=661)';
            }else{
                $where=$this->where.' and (kfs=895 or lpmc=661)';
            }
            
            switch ($type){
                case 'XYV':
                    $sql1 = $this->setXYVAxisSqlExp(723,$where);
                    break;
                case 'XV':
                    $sql1 = $this->setXVAxisSqlExp(723,$where);
                    break;
                case 'YV':
                    $sql1 = $this->setYVAxisSqlExp(723,$where);
                    break;
            }
            
            //处理招商
            if(empty($this->where)){
                $where=' where kfs=895';
            }else{
                $where=$this->where.' and kfs=895';
            }
            switch ($type){
                case 'XYV':
                    $sql2 = $this->setXYVAxisSqlExp(894,$where);
                    break;
                case 'XV':
                    $sql2 = $this->setXVAxisSqlExp(894,$where);
                    break;
                case 'YV':
                    $sql2 = $this->setYVAxisSqlExp(894,$where);
                    break;
            }
            
            $sql = ' union all '.$sql1.' union all '.$sql2;
        }else{
            $sql='';
        }
        return $sql;
    }

     /**
     * @方法：提取同时存在XYV三维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setXYVAxisSql(){
        $axix = $this->getArrayMerge($this->xAxis,$this->yAxis);
        $group = implode(',', $axix);

        if($this->isChangeColumn($axix)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumn($this->xAxis)
                .','
                .$this->parseOriginSelectColumn($this->yAxis)
                .','
                .$this->parseOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$this->where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->parseScrapeChangeColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($axix);
        }else{
            $sql = 'select '
                    .$this->parseScrapeSelectColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$this->where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @方法：提取只存在XV二维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setXVAxisSql(){
        $group = implode(',', $this->xAxis);

        if($this->isChangeColumn($this->xAxis)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumn($this->xAxis)
                .','
                .$this->parseOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$this->where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->parseScrapeChangeColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->getCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($this->xAxis);
        }else{
            $sql = 'select '
                    .$this->parseScrapeSelectColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$this->where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @方法：提取YV二维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setYVAxisSql(){
        $group = implode(',', $this->yAxis);

        if($this->isChangeColumn($this->yAxis)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumn($this->yAxis)
                .','
                .$this->parseOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$this->where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($this->yAxis);
        }else{
            $sql = 'select '
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$this->where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @说明：将条件参数转换成bind参数数组。仅用于存储过程的调用
     * @param type $DataBaseParam
     * @return string
     */
    private function setStoredBind($DataBase,$DataBaseParam){
        $bind=array();
        $array = explode(';', $DataBaseParam);
        foreach ($array as $v){
            $line = explode('=', $v);
            $key = $line[0];
            $val = $line[1];
            
            if($val=='T'){//常量：表示数据表名
                $bind[$key]=$DataBase;
            }else{
                if(strpos($val,':')){//存在区间段值时，设定0为最小值，1为最大值
                    $arr = explode(':', $val);
                    $k = strtoupper($arr[0]);
                    $values = $this->condition[$k][$arr[1]];
                    $bind[$key]=  !empty($values)?$values:'';
                }else{
                    $k = strtoupper($val);
                    if(isset($this->condition[$k])){
                        if(is_array($this->condition[$k])){
                            $bind[$key] = implode(',', $this->condition[$k]);
                        }else{
                            $bind[$key] = $this->condition[$k];
                        }
                    }else{
                        $bind[$key]=  '';
                    }

                }
            }
        }
        return $bind;
    }
    
    private function setSockStoredData($where){
        //$table = 'temp_'.date('Ymdhis');
        //$this->tempTableArr[] = $table;
        
        $DataBase = 'Temp_'.date("Ymdhis");
        $this->datatable = $DataBase; 
        $this->tempTableArr[] = $DataBase;
                
//        $param = ':table=T;:StartDate=cjrq:0;:EndDate=cjrq:1;:qy=qy;:zt=zt;:kfs=kfs;:lpmc=lpmc;:yt=yt;:xfyt=xfyt;:hx=hx';
//        
//        $bind=$this->setStoredBind($DataBase,$param);
//                var_dump($bind);exit();
//        M()->prepare('countStock',$bind);
        
        $StartDate = isset($where['StartDate'])?$where['StartDate']:'2010-01-01';
        $EndDate = isset($where['EndDate'])?$where['EndDate']:date('Y-m-d');
        $qy = isset($where['QY'])?$where['QY']:'';
        $zt = isset($where['ZT'])?$where['ZT']:'';
        $kfs = isset($where['KFS'])?$where['KFS']:'';
        $lpmc = isset($where['LPMC'])?$where['LPMC']:'';
        $yt = isset($where['YT'])?$where['YT']:'';
        $xfyt = isset($where['XFYT'])?$where['XFYT']:'';
        $hx = isset($where['HX'])?$where['HX']:'';
//
//        //$stime=microtime(true); //获取程序开始执行的时间  
        $param = array(
            ':table'=>$DataBase,
            ':StartDate'=>$StartDate,
            ':EndDate'=>$EndDate,
            ':qy'=>$qy,
            ':zt'=>$zt,
            ':kfs'=>$kfs,
            ':lpmc'=>$lpmc,
            ':yt'=>$yt,
            ':xfyt'=>$xfyt,
            ':hx'=>$hx);
        M()->query("exec countStock '$this->datatable','$StartDate','$EndDate','$qy','$zt','$kfs','$lpmc','$yt','$xfyt','$hx'");
        //M()->prepare('countStock',$param);
                //var_dump($param);exit();
        //M()->execute("exec countStock",$param);
        //$etime=microtime(true);//获取程序执行结束的时间  
        //$total=$etime-$stime;   //计算差值 
        //echo $total;
        
    }

    /**
     * @方法：提取Stock二维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setStockYVAxisSql(){
        $group = implode(',', $this->yAxis);
        $where = $this->where;
        
        //当为存储过程时，需要执行此
        if($this->DataType == 'STORED'){            
            $where = $this->setSockStoredData($where);
        }   
        //echo $this->datatable;exit();
        if($this->isChangeColumn($this->yAxis)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumn($this->yAxis)
                .','
                .$this->parseStockOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getStockCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($this->yAxis);
        }else{
            $sql = 'select '
                    .$this->setChangColumn($this->yAxis)
                    .','
                    .$this->getStockCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @方法：提取同时存在XYV三维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setXYVAxisSqlExp($id,$where){
        $axix = $this->getArrayMerge($this->xAxis,$this->yAxis);
        $group = implode(',', $axix);

        if($this->isChangeColumn($axix)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumnExp($id,$this->xAxis)
                .','
                .$this->parseOriginSelectColumnExp($id,$this->yAxis)
                .','
                .$this->parseOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->parseScrapeChangeColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->setChangColumnExp($id,$this->yAxis)
                    .','
                    .$this->getCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($axix);
        }else{
            $sql = 'select '
                    .$this->parseScrapeSelectColumnExp($id,$this->xAxis).' as xAxis'
                    .','
                    .$this->setChangColumnExp($id,$this->yAxis)
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @方法：提取只存在XV二维度的数据源
     * @参数：维度数组
     * @返回：SQL
     */
    private function setXVAxisSqlExp($id,$where){
        $group = implode(',', $this->xAxis);

        if($this->isChangeColumn($this->xAxis)){//存在转换
            //1.解析数据源SQL
            $r = 'select '
                .$this->parseOriginSelectColumnExp($id,$this->xAxis)
                .','
                .$this->parseOriginCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$where;
            
            //2.生成表头数据源
            $sql = 'select '
                    .$this->parseScrapeChangeColumn($this->xAxis).' as xAxis'
                    .','
                    .$this->getCountColumn($this->vAxis)
                    .' from '
                    .'('.$r.') as t'
                    .' group by '
                    .$this->parseScrapeGroupBy($this->xAxis);
        }else{
            $sql = 'select '
                    .$this->parseScrapeSelectColumnExp($id,$this->xAxis).' as xAxis'
                    .','
                    .$this->getNotCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where
                    .' group by '
                    .$group;
        }
        return $sql;
    }
    
    /**
     * @方法：设置排行榜数据源---单维度时
     * @参数：维度数组
     * @返回：SQL
     */
    private function setTopSingleAxisSql(){
        $yAxis = $this->yAxis[0];
        
        //1.解析数据源SQL
        $r = 'select '
            .$this->parseOriginSelectColumn($this->yAxis)
            .','
            .$this->parseOriginCountColumn($this->vAxis)
            .' from '
            .$this->datatable
            .$this->where;


        //1.5追加特殊数据
        if($yAxis == 'KFS'){

            if(empty($this->where)){
                $where=' where (kfs=895 or lpmc=661)';
            }else{
                $where=$this->where.' and (kfs=895 or lpmc=661)';
            }
            $r .= ' union all '
                    .'select '
                    .'723 as KFS'
                    .','
                    .$this->parseOriginCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where;

            //处理招商
            if(empty($this->where)){
                $where=' where kfs=895';
            }else{
                $where=$this->where.' and kfs=895';
            }

            $r .= ' union all '
                    .'select '
                    .'894 as KFS'
                    .','
                    .$this->parseOriginCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where;
        }
        
        //2.生成表头数据源
        $sql = 'select '
                .$this->setChangColumn($this->yAxis)
                .','
                .$this->getCountColumn($this->vAxis)
                .' from '
                .'('.$r.') as t'
                .' group by '
                .$this->parseScrapeGroupBy($this->yAxis);

        return $sql;
    }
    
    /**
     * @方法：设置排行榜数据源---多个维度时
     * @参数：维度数组
     * @返回：SQL
     */
    private function setTopMultiAxisSql(){        
        //1.解析数据源SQL
        $r = 'select '
            .$this->parseOriginSelectColumn($this->yAxis)
            .','
            .$this->parseOriginCountColumn($this->vAxis)
            .' from '
            .$this->datatable
            .$this->where;


        //1.5追加特殊数据
        if($this->setIsExceptionalCount()){

            if(empty($this->where)){
                $where=' where (kfs=895 or lpmc=661)';
            }else{
                $where=$this->where.' and (kfs=895 or lpmc=661)';
            }
            $r .= ' union all '
                    .'select '
                    .$this->parseOriginSelectColumnExp(723,$this->yAxis)
                    .','
                    .$this->parseOriginCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where;

            //处理招商
            if(empty($this->where)){
                $where=' where kfs=895';
            }else{
                $where=$this->where.' and kfs=895';
            }

            $r .= ' union all '
                    .'select '
                    .$this->parseOriginSelectColumnExp(894,$this->yAxis)
                    .','
                    .$this->parseOriginCountColumn($this->vAxis)
                    .' from '
                    .$this->datatable
                    .$where;
        }

        //2.生成表头数据源
        $sql = 'select '
                .$this->setChangColumn($this->yAxis)
                .','
                .$this->getCountColumn($this->vAxis)
                .' from '
                .'('.$r.') as t'
                .' group by '
                .$this->parseScrapeGroupBy($this->yAxis);
        
        return $sql;
    }
    
    /**
     * @方法：提取YV二维度的数据源时，纵向汇总数
     * @参数：维度数组
     * @返回：SQL
     */
    private function setYVAxisSumSql(){
        $sql = 'select '
                .$this->getNotCountColumn($this->vAxis)
                .' from '
                .$this->datatable
                .$this->where;
        
        return $sql;
    }
    
    
    /**
     * @方法：根据V维度解析统计字段
     * @参数：V维度
     * @返回：返回V维度数组
     */
    protected function setCountHeader(){
        $head=array();
        
        foreach($this->vAxis as $val){
            if($val['c']=='COUNT'){
                $c='SUM';
            }else{
                $c=$val['c'];
            }
            
            $k = $this->setCountColumnFormats($val['k'],$c,$val['u']);
            $head[$k] = $this->setCountColumnName($val['k'],$c,$val['u']);
        }

        return $head;
    }
    
    /**
     * @方法：根据Stock析统计字段
     * @参数：V维度
     * @返回：返回V维度数组
     */
    protected function setStockCountHeader(){
        $head=array();
        
        foreach($this->vAxis as $val){
            if($val['c']=='COUNT'){
                $c='SUM';
            }else{
                $c=$val['c'];
            }
            
            $k = $this->setCountColumnFormats($val['k'],$c,$val['u']);
            $head[$k] = $this->setCountColumnName($val['k'],$c,$val['u']);
        }

        return $head;
    }
    
    
    /**
     * @方法：处理表头数组结构--表格输出结构化
     * @参数：表头数组参数
     * @返回：表头层级数组
     */
    private function setHeaderFormat($id,$code,$name){
        if($this->levelNums>1){
            $iArr = explode('@', $id);//代号数组
            $nArr = explode('@', $name);//名称数组
            
            $i=0;
            foreach ($iArr as $val){
                if($i + 1 == $this->levelNums){//最后一级
                    $this->headData['RE'][$i][]=$this->setHeadOutFormat($nArr[$i],$code,$id,'');
                }else{
                    $this->headData['RE'][$i][]=$nArr[$i];
                }
                ++$i;
            }
        }else{
            $this->headData['RE'][0][]=$this->setHeadOutFormat($name,$code,$id,'');
        }
    }

    /**
     * @方法：处理表头左侧数据
     * @参数：y维度值
     * @返回：y维度表头数据
     */
    private function setHeadyAxis(){
        if($this->yNums>0){//存在y维度时
            foreach ($this->yAxis as $key){
                if($this->levelNums>1){
                    for($i=0;$i<$this->levelNums;$i++){
                        if($i == $this->levelNums - 1){//最后一层
                            $this->headData['RE'][$i][]=$this->setHeadOutFormat($this->rulesArr[$key]['FieldName'],$key,$key,'');
                        }else{
                            $this->headData['RE'][$i][]=$key;//$this->rulesArr[$key]['FieldName'];//$this->setHeadOutFormat($this->rulesArr[$key]['FieldName'],$key,'','');
                        }
                    }
                }else{
                    $this->headData['RE'][0][]=$this->setHeadOutFormat($this->rulesArr[$key]['FieldName'],$key,$key,'');
                }
            }
        }else{
            $this->headData['RE'][0][]=$this->setHeadOutFormat('数据统计','yAxis','yAxis','');
        }
    }
    
    /**
     * @方法：处理表头右侧汇总数据
     * @参数：y维度值
     * @返回：y维度表头数据
     */
    private function setHeadSum(){
        $sum=array();
        
        foreach ($this->vAxis as $val){
            if($val['c']!='RATE'){
                $code = 'T_'.$this->setCountColumnFormats($val['k'], $val['c'], $val['u']);
                if($this->levelNums == 1){
                    $sum[0][] = $this->setHeadOutFormat('合计',$code,$code,'');
                }else{
                    $sum[0][] = $this->setHeadOutFormat($this->setCountColumnName($val['k'], $val['c'], $val['u']),$code,$code,'');
                }
            }
        }

        $colspan = count($sum[0]);
        
        if($this->levelNums>1){
            for($i=0;$i<$colspan;$i++){
                $sum[1][] = '合计';
            }            
        }
        
        return $sum;
    }
    
    /**
     * @方法：处理数字格式---返回小数位置
     * @参数：数字
     * @返回：格式化的数字
     */
    protected function setNumberFormat($key,$num){
        if(empty($num)){
            return 0;
        }else{
            if(strpos($key,'_')){//统计方式
                $str = explode('_', $key);
                $number = empty($this->rulesArr[$str[0]]['FieldPrecision'])?0:$this->rulesArr[$str[0]]['FieldPrecision'];
            }else{//表头
                switch (substr($key,0,1)){
                    case 'A':
                        $number = 0;
                        break;
                    case 'B':
                        $number = 1;
                        break;
                    case 'C':
                        $number = 2;
                        break;
                    case 'D':
                        $number = 3;
                        break;
                    case 'E':
                        $number = 4;
                        break;
                    case 'F':
                        $number = 5;
                        break;
                    case 'G':
                        $number = 6;
                        break;
                    case 'H':
                        $number = 7;
                        break;
                    case 'I':
                        $number = 8;
                        break;
                    case 'J':
                        $number = 9;
                        break;
                    case 'K':
                        $number = 10;
                        break;
                    default :
                        $number = 0;
                        break;
                }
            }
            return round($num,$number);
        }
    }
    
    /**
     * @方法：根据统计方式关键字，判断代号字母，主要用于控制显示的小数位
     * @参数：统计方式
     */
    protected function setCountCode($key){
        $base=array('A','B','C','D','E','F','G','H','I','J','K');//最大10位小数
        
        if(strpos($key,'_')){
            $str = explode('_', $key);
            $key=$str[0];
        }
        
        if(empty($this->rulesArr[$key]['FieldPrecision'])){
            $code = 'A';
        }else{
            $code = $base[$this->rulesArr[$key]['FieldPrecision']];
        }  
        return $code;
    }

    
    /**
     * @方法：根据X维度解析表头数组
     * @参数：X维度与V维度
     * @返回：返回表头数组
     */
    private function setAxisHeader($sql){
        $head=array();
        $isContrastAxis = $this->isContrastAxis();//是否需要对照表
        
        if($this->yNums>0){
            if($this->vNums>1){
                $count = $this->setCountHeader();
                if($this->xNums>0 && $this->yNums>0){
                    $isMulti = true;
                }else{
                    $isMulti = false;
                }
            }else{
                $count[] = $this->vAxis[0]['k'].'_'.$this->vAxis[0]['c'];
                $cName = $this->setCountColumnName($this->vAxis[0]['k'], $this->vAxis[0]['c'], $this->vAxis[0]['u']);
                $isMulti = false;
                $singleCode=$this->setCountCode($this->vAxis[0]['k']);
            }        
        }else{
            $count[] = $this->vAxis[0]['k'].'_'.$this->vAxis[0]['c'];
            $cName = $this->setCountColumnName($this->vAxis[0]['k'], $this->vAxis[0]['c'], $this->vAxis[0]['u']);
            $isMulti = false;
            $singleCode=$this->setCountCode($this->vAxis[0]['k']);
        }
        
        //左侧数据
        $this->setHeadyAxis();
        
        //1.取数
        $db = new SqlsrvController();
        $stmt = $db->query($sql);
        $i=0;
        $isFootOrRate = $this->isFootOrRate();
        
        while ($row = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC)){
            if($isContrastAxis){//有对照
                if(!$isMulti){
                    $code = $singleCode.$i;
                    $this->setHeaderFormat($row['id'],$code,$row['xName']);
                    
                    if($isFootOrRate){
                        $this->footData[$code] = $this->setNumberFormat($code,$row[$count[0]]);
                    }
                    
                    $BM[] = '['.$row['xAxis'].']'.' as '.$code;//交叉结果Select字段，带别名
                    $PV[] = '['.$row['xAxis'].']';//交叉字段
                    $ID[$code] = '['.$row['xAxis'].']';//
                    ++$i;
                }else{
                    foreach ($count as $k=>$v){
                        $id=$row['id'].'@'.$k;
                        $key=$row['xAxis'].'@'.$k;
                        $val=$row['xName'].'@'.$v;
                        $code = $this->setCountCode($k).$i;
                        
                        //$HD[$id]=array($key,$val);
                        $this->setHeaderFormat($id,$code,$val);
                        
                        if($isFootOrRate){
                            $this->footData[$code] = $this->setNumberFormat($code,$row[$k]);
                        }
                        
                        $BM[] = '['.$key.']'.' as '.$code;//交叉结果Select字段，带别名
                        $PV[] = '['.$key.']';//交叉字段
                        $ID[$code] = '['.$key.']';//交叉字段
                        ++$i;
                    }
                } 
            }else{//无对照
                if(!$isMulti){
                    $code = $singleCode.$i;
                    
                    //$HD[$id]=array($row['xAxis'],$row['xAxis']);
                    $this->setHeaderFormat($row['xAxis'],$code,$row['xAxis']);
                    
                    if($isFootOrRate){
                        $this->footData[$code] = $this->setNumberFormat($code,$row[$count[0]]);
                    }
                    
                    $BM[] = '['.$row['xAxis'].']'.' as '.$code;//交叉结果Select字段，带别名
                    $PV[] = '['.$row['xAxis'].']';//交叉字段
                    $ID[$code] = '['.$row['xAxis'].']';//交叉字段
                    ++$i;
                }else{
                    foreach ($count as $k=>$v){
                        $code = $this->setCountCode($k).$i;
                        $key=$row['xAxis'].'@'.$k;
                        $val=$row['xAxis'].'@'.$v;
                        
                        //$HD[$id]=array($key,$val);
                        $this->setHeaderFormat($key,$code,$val);
                        
                        if($isFootOrRate){
                            $this->footData[$code] = $this->setNumberFormat($code,$row[$k]);
                        }
                        
                        $BM[] = '['.$key.']'.' as '.$code;//交叉结果Select字段，带别名
                        $PV[] = '['.$key.']';//交叉字段
                        $ID[$code] = '['.$key.']';//交叉字段
                        ++$i;
                    }
                }
            }
        }

        //多层表头时，需要处理上级表头数据
        if($this->levelNums > 1){
            for($i=0;$i<$this->levelNums - 1;$i++){
                $LinesNumsArr = array_count_values($this->headData['RE'][$i]);//计算每个值重复的次数
                
                unset($this->headData['RE'][$i]);//清除此层数据
                
                //重新追加
                foreach ($LinesNumsArr as $title=>$colspan){
                    if(in_array($title,$this->yAxis)){//左侧数据
                        $this->headData['RE'][$i][]=$this->setHeadOutFormat($this->rulesArr[$title]['FieldName'],$title,'',$colspan);
                    }else{
                        $this->headData['RE'][$i][]=$this->setHeadOutFormat($title,'','',$colspan);
                    }                    
                }
            }
            
            //对表头数组进行排序
            asort($this->headData['RE']);
        }
        
        //若需要横向统计时，追加合计字段
        if($this->count['isXSum'] == 1){
            $total = $this->setHeadSum(); 

            if($this->levelNums>1){
                $colspan = count($total[1]);
                
                for($i=0;$i < $this->levelNums - 1;$i++){
                    $this->headData['RE'][$i][]=$this->setHeadOutFormat($total[1][0],'SUM','',$colspan);
                }
                
                //底层
                foreach ($total[0] as $val){
                    $this->headData['RE'][$this->levelNums-1][]=$val;
                }
            }else{
                foreach ($total[0] as $val){
                    $this->headData['RE'][0][]=$val;
                }
            }
        }
        
        //解除数据库连接
        $db->free();
        
        if($this->xNums>0){//存在X维度
            $this->headData['BM']=$BM;
            $this->headData['PV']=$PV;
            $this->headData['ID']=$ID;
        }

        return $this->headData['RE'];
    }
    
    /**
     * @方法：获取表头数组:包括各种情况下的表头数组
     * @参数：表头取数SQL
     * @返回：数组
     */
    protected function getHeadData(){
        $head=array();
        if($this->xNums>0){//存在表头
            $head=$this->setAxisHeader($this->getHeadSources());
        }else{//不存在表头
            if($this->type == 1){//统计类型：排行榜
                if($this->yNums == 1){
                    $this->headData['RE'][0][] = $this->setHeadOutFormat('排名','rank','rank','');
                }                
            }
            //y维度表头
            $this->setHeadyAxis();
            
            //表体表头
            foreach ($this->setCountHeader() as $key=>$val){
                $this->headData['RE'][0][] = $this->setHeadOutFormat($val,$key,$key,'');
            }            
            $head = $this->headData['RE'];
            
            if($this->isFootOrRate()){
                $sums = M()->query($this->setYVAxisSumSql());
                
                foreach($sums[0] as $k=>$v){
                    $this->footData[$k]=$this->setNumberFormat($k,$v);
                }
            }
        }
        unset($this->headData['RE']);
        
        return $head;
    }
    
    /**
     * @方法：获取表头数组:包括各种情况下的表头数组
     * @参数：表头取数SQL
     * @返回：数组
     */
    protected function getStockHeadData(){
        $head=array();
        //y维度表头
        $this->setHeadyAxis();

        //表体表头
        foreach ($this->setCountHeader() as $key=>$val){
            $this->headData['RE'][0][] = $this->setHeadOutFormat($val,$key,$key,'');
        }            
        //$this->headData['RE'][0][] = $this->setHeadOutFormat('结存','QMTM_SUM','QMTM_SUM','');
        $head = $this->headData['RE'];

        
//        if($this->isFootOrRate()){
//            $sums = M()->query($this->setYVAxisSumSql());
//
//            foreach($sums[0] as $k=>$v){
//                $this->footData[$k]=$this->setNumberFormat($k,$v);
//            }
//        }
        
        unset($this->headData['RE']);
        
        return $head;
    }
    
    /**
     * @方法：判断表头X维度是否存在对照或区间数据[有对照时需要加关联表]
     * @参数：X维度
     * @返回：true为包含，false为不包含
     */
    private function isContrastAxis(){
        if($this->xNums>0){
            $i=0;
            foreach($this->xAxis as $key){
                switch ($this->rulesArr[$key]['FieldType']){
                    case 3:
                    case 4:
                        break;
                    case 1:
                    case 2:
                        ++$i;
                        break;
                }
            }
            if($i>0){
                return true;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
    
    /**
     * @方法：判断是否存在计算占比的方式
     * @参数：V维度
     * @返回：true为包含，false为不包含
     */
    private function isRateAxis(){
        if($this->vNums>0){
            $i=0;
            foreach($this->vAxis as $val){
                if($val['c']=='RATE'){
                    ++$i;
                }
            }
            
            if($i>0){
                return true;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }
    
    /**
     * @方法：将两个维度数组合并成一个数组
     * @参数：两个维度的数组
     * @返回：合并之后的数组
     */
    private function getArrayMerge($arr1,$arr2){
        $result=array();
        
        if(!empty($arr1)&&!empty($arr2)){
            $result=array_merge($arr1,$arr2);
        }elseif(!empty($arr1)&&empty($arr2)){
            $result=$arr1;
        }elseif(empty($arr1)&&!empty($arr2)){
            $result=$arr2;
        }
        
        return $result;
    }
    
    /**
     * @方法：根据维度参数解析单一统计值时的交叉SQL
     * @参数：维度参数
     * @返回：返回SQL语句
     */
    private function setSingleCountPivotSql(){
        $x= implode(',', $this->headData['BM']);
        $y= implode(',', $this->yAxis);
        $v= $this->vAxis[0]['c'].'('.$this->vAxis[0]['k'].'_'.$this->vAxis[0]['c'].')';
        $p= implode(',', $this->headData['PV']);

        $rs='select '.$y.','.$x
             .' from ('
             .$this->getDataSources()
             .') as rt '
             .' PIVOT('.$v.' for '
             .' xAxis in('.$p.')'
             .' ) as p';
        
        $Inner = $this->setDataInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);

        $head = implode(',', array_keys($this->headData['ID']));

        //读取中文及排序
        $sql='select '
              .$id.' as id'
              .','
              .$select
              .','
              .$head
              .' from ('
              .$rs
              .') as pt '
              .$inner
              .' order by '
              .$order;
        //echo $sql;
        return $sql;
    }
    
    /**
     * @方法：创建最终结果集的交叉表
     * @用法：当统计字段为多个时，将创建临时表，必须同时有XYV（二个以上值时）
     * @参数：XYV维度值
     */
    private function createPivotDataTempTable(){
        //1.初始化表名
        $temptable='Temp_'.date("Ymdhis");
        $cArr=array();
        
        if($this->yNums>0){
            foreach ($this->yAxis as $v){
                $cArr[]=$v.' varchar(40) ';
            }
        }else{//当无Y维度时，增加一列
            $cArr[]='yAxis varchar(40) ';
        }
        
        if($this->xNums>0){
            $cArr[]='xAxis varchar(200) ';
        }
        
        if($this->vNums>0){
            $cArr[]='counts decimal(18,'.$this->decimal.') ';
        }
        
        $columnStr=implode(",", $cArr);
        $sql='CREATE TABLE '.$temptable.'('
                .$columnStr
                .')';

        if(M()->execute($sql)){
            //将创建的临时表名写入数组
            $this->tempTableArr[]=$temptable;
            
            return $temptable;
        }else{
            $this->error=array('D1'=>sqlsrv_errors());
        }
    }
    
    /**
     * @方法：根据V数组，解析成生成插入到临时表数据的SQL
     * @参数：统计字段数组，包含键值及数据[此时一般是含有X,Y,V三个维度]
     * @返回：SQL字符串
     */
    private function insertTempTableData(){
        //1.创建临时表
        $temptable=$this->createPivotDataTempTable();//返回临时表名

        //2.解析V字段数组

        $y = implode(',',$this->yAxis);
        
        if($this->vNums>1){
            foreach ($this->vAxis as $val){
                $sql='';
                if($this->xNums == 1){
                    $xAxis = 'cast(xAxis as varchar(30))'."+'@'+'".$val['k'].'_'.$val['c']."' as xAxis";
                }else{
                    $xAxis = 'xAxis'."+'@'+'".$val['k'].'_'.$val['c']."' as xAxis";
                }
                $sql='insert into '.$temptable.'(xAxis,'.$y.',counts) ';
                $sql.=' select '.$xAxis.','.$y.','.$val['k'].'_'.$val['c'].' from ('.$this->getDataSources().') as fromtable ';

                M()->execute($sql);
            }
        }else{
            $sql='insert into '.$temptable.'(xAxis,'.$y.',counts) ';
            $sql.=' select xAxis,'.$y.','.$this->vAxis[0]['k'].'_'.$this->vAxis[0]['c'].' from ('.$this->getDataSources().') as fromtable ';

            M()->execute($sql);
        }        
        
        return $temptable;//将临时表名返回
    }
    
    
    /**
     * @方法：根据维度参数解析多个统计值时的交叉SQL
     * @参数：维度参数
     * @返回：返回SQL语句
     */
    private function setMultiCountPivotSql(){
        $temptable = $this->insertTempTableData();

        $x= implode(',', $this->headData['BM']);
        $y= implode(',', $this->yAxis);
        $p= implode(',', $this->headData['PV']);

        //交叉数据
        $rs='select '.$y.','.$x
             .' from '
             .$temptable
             .' PIVOT(sum(counts) for '
             .' xAxis in('.$p.')'
             .' ) as p';


        $Inner = $this->setDataInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);

        $head = implode(',', array_keys($this->headData['ID']));
        
        //读取中文及排序
        $sql='select '
              .$id.' as id'
              .','
              .$select
              .','
              .$head
              .' from ('
              .$rs
              .') as pt '
              .$inner
              .' order by '
              .$order;

        return $sql;      
    }
    
   
    
    /**
     * @方法：处理无x维度时的交叉分析
     * @参数：维度参数
     * @返回：SQL     * 
     */
    private function setCountUnpivotSql(){
        $countList = implode(',', array_keys($this->setCountHeader()));
        
        $unpivot='select xAxis,yAxis,count '
                .' from ('
                .$this->getDataSources()
                .') as rrtt '
                .' unpivot('
                .'count for yAxis in('
                .$countList
                .')'
                .') as upt';
        
        //转换中文名称
        $case='(case yAxis ';
        foreach ($this->setCountHeader() as $key=>$val){
            $case.=" when '".$key."' then '".$val."'";
        }
        $case.=' end) as yAxis';
        
        $BM =  implode(',', $this->headData['BM']);
        $PV =  implode(',', $this->headData['PV']);

        //最终交叉表
        $sql='select yAxis as id'.','.$case.','.$BM
              .' from ('
              .$unpivot
              .') as ttbb '
              .'pivot(sum(count) for xAxis in('
              .$PV
              .')) as pp';
        return $sql;
    }
    
    /**
     * @方法：处理当不存在X维度时统计数据
     * @参数：维度
     * @返回：SQL
     */
    private function setSelectCountSql(){
        $rs = $this->getDataSources();

        $Inner = $this->setDataInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);

        $head = implode(',', array_keys($this->setCountHeader()));
        
        //读取中文及排序
        $sql='select '
              .$id.' as id'
              .','
              .$select
              .','
              .$head
              .' from ('
              .$rs
              .') as pt '
              .$inner
              .' order by '
              .$order;

        return $sql;
    }
    
    /**
     * @方法：处理Stock时统计数据
     * @参数：维度
     * @返回：SQL
     */
    protected function setStockDataSql(){
        $rs = $this->setStockYVAxisSql();

        $Inner = $this->setStockInner($this->yAxis);
        $id = implode("+'@'+", $Inner['id']);
        $select = implode(',', $Inner['select']);
        $inner = implode(' ', $Inner['inner']);
        $order = implode(',', $Inner['order']);
        
        $head = implode(',', array_keys($this->setStockCountHeader()));
        
        //读取中文及排序
        $sql='select '
              .$id.' as id'
              .','
              .$select
              .','
              .$head
              .' from ('
              .$rs
              .') as pt '
              .$inner
              .' order by '
              .$order;
//echo $sql;exit();
        return $sql;
    }
    

    /**
     * @方法：处理交叉数据源
     * @参数：sql
     * @返回：数组
     */
    protected function getPivotData(){
        if($this->xNums > 0){//存在X
            if($this->yNums > 0){
                if($this->vNums > 1){
                    $sql = $this->setMultiCountPivotSql();
                }elseif($this->vNums == 1){
                    $sql = $this->setSingleCountPivotSql();
                }else{
                    $sql = '';
                }
            }else{//不存在Y
                if($this->vNums > 0){
                    $sql = $this->setCountUnpivotSql(); 
                }else{
                    $sql = '';
                }
            }
        }else{//不存在X
            if($this->yNums > 0){
                if($this->vNums > 0){
                    $sql = $this->setSelectCountSql(); 
                }else{
                    $sql = '';
                }
            }else{
                $sql = '';
            }
        }
        return $sql;
    }
    

    /**
     * @方法：获取分析数据源
     * @用途：读取原始数据
     * @返回：提取数据源的SQL
     * @access protected
     */
    protected function getTopData(){
        //初始化值
        $topNum=$this->ranking['topNum']?$this->ranking['topNum']:10;
        $orderBy=$this->ranking['topColumn'][0].'_'.$this->ranking['topColumn'][1].' '.$this->ranking['topColumn'][2];

        //唯一个分组值时
        if($this->yNums==1){
            $sql = $this->getTopSingleDataSources($topNum,$orderBy);
        }else{//多个分组时
            $sql = $this->getTopMultiDataSources($topNum,$orderBy);
        }
        return $sql;
    }
    
    /**
     * @方法：解析统计字段显示的中文名称
     * @参数：统计字段$key,$count,$uom
     * @返回：返回统计字段
     */
    protected function setCountColumnName($k,$c,$u){
        if($c=='RATE'){
            return '市场份额%（'.$this->setColumnName($k).')';
        }else{
            return $this->setColumnName($k).'('.$this->setCountName($c).','.$this->setUomName($u).')';
        }
    }
    
    /**
     * @方法：解析每个字段的中文名称
     * @参数：统计字段$key
     * @返回：返回统计字段
     */
    protected function setColumnName($key){
        return $this->rulesArr[$key]['FieldName'];
    }
    
    /**
     * @方法：解析每个计量单位的名称
     * @参数：统计字段$uom
     * @返回：返回统计字段
     */
    protected function setUomName($uom){
        return $this->uomArr[$uom]['UomName'];
    }
    
    /**
     * @方法：解析每个统计方式的名称
     * @参数：统计字段$count
     * @返回：返回统计字段
     */
    protected function setCountName($count){
        return $this->countArr[$count]['CountName'];
    }
    
    /**
     * @方法：标准化表头输出格式数组
     * @参数：表头参数
     * @返回：格式数组
     */
    protected function setHeadOutFormat($title,$field,$id,$colspan){
        if(!empty($title)){
            $res['t']=$title;
        }
        if(!empty($field)){
            $res['f']=$field;
        }
        if(!empty($id)){
            $res['i']=$id;
        }
        if(!empty($colspan)&&$colspan>1){
            $res['c']=$colspan;
        }
        return $res;
    }
    
    
    /*
     * 方法：删除临时表
     */
    protected function doDropTempTable(){
       if(is_array($this->tempTableArr)){
           $sql='';
           foreach($this->tempTableArr as $val){
              $sql.= "IF  EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='".$val."')";
              $sql.=" DROP TABLE ".$val.";"; 
           }
          M()->execute($sql); 
       }      
       return false;
    }
    
    /**
     * @方法：设定左侧固定列代号
     * @参数：y维度数组
     * @返回：一维数组
     */
    protected function getFixedColumn(){
        if($this->yNums > 0){//存在y维度
            if($this->type == 1){//排行榜
                $fixed[]='rank';
            }
            foreach ($this->yAxis as $key){
                $fixed[]=$key;
            }
        }else{//锁定纵向值
            $fixed[]='yAxis';
        }
        
        return $fixed;
    }
    /**
     * @方法：根据传入的参数，判断行颜色不同的列
     * @参数：y维度
     * @返回：数组
     */
    protected function getColorColumn(){
        if($this->yNums > 0){//存在y维度
            if($this->type == 1){//排行榜
                $color[]='rank';
            }
            foreach ($this->yAxis as $key){
                $color[]=$key;
            }
        }else{//锁定纵向值
            $color[]='yAxis';
        }
        return $color;
    }
    
    /**
     * @方法：根据传入的参数，判断列中需要合并的表头列
     * @参数：y维度
     * @返回：数组
     */
    protected function getMergeHeadColumn(){
        $rowspan=array();
        if($this->yNums > 0){//存在y维度            
            foreach($this->yAxis as $key){
                $rowspan[] = $key;
            }
        }else{//锁定纵向值
            $rowspan[]='yAxis';
        }
        if($this->count['isXSum'] == 1){
            $rowspan[]='SUM';
        }
        return $rowspan;
    }
    
    /**
     * @方法：根据传入的参数，判断列中需要合并的列
     * @参数：y维度
     * @返回：数组
     */
    protected function getMergeColumn(){
        if($this->yNums > 1){//存在y维度
            $rowspan=array();
            $j=1;
            foreach($this->yAxis as $key){
                if($j < $this->yNums ){
                    $rowspan[] = $key;
                }
                ++$j;
            }
        }
        return $rowspan;
    }
    
    /**
     * @方法：根据传入的参数，判断表头维度
     * @参数：x维度
     * @返回：数组
     */
    protected function getHeadLevel(){
        $rowspan=array();
        if($this->xNums > 0){//存在x维度
            if($this->yNums > 0){
                foreach($this->xAxis as $key){
                    $rowspan[] = $key;
                }
                    
                if($this->vNums>1){//多个统计值
                    $rowspan[] = 'COUNT';
                }
            }else{
                foreach($this->xAxis as $key){
                    $rowspan[] = $key;
                }
            }
        }else{
            $rowspan[] = 'COUNT';
        }
        return $rowspan;
    }
    
    /**
     * @方法：根据传入的参数，判断横向维度值
     * @参数：y维度
     * @返回：数组
     */
    protected function getRowsLevel(){
        $rowspan=array();
        if($this->yNums > 0){//存在y维度
            foreach($this->yAxis as $key){
                $rowspan[] = $key;
            }
        }else{
            $rowspan[] = 'COUNT';
        }
        return $rowspan;
    }
    
     /**
     * 异常情况返回机制
     * @access public $result = false表示不输出异常原因
     * @return string
     */
    public function debug($result = false){
        if($result){
            $hLine[]=array('t'=>'错误代号','f'=>'code');
            $hLine[]=array('t'=>'错误信息','f'=>'message');
            $hItem[]=$hLine;
            
            foreach ($this->error as $code=>$message){
                $rLine[]=array('t'=>$code,'f'=>'code');
                $rLine[]=array('t'=>$message,'f'=>'message');
            }
            $rItem[]=$rLine;
            
            $result = array('h'=>array('i'=>$hItem),'r'=>array('i'=>$rItem));
        }else{
            $hItem[][]=array('t'=>'','f'=>'');       
            $rItem[][]=array('t'=>'','f'=>'');
            
            $result = array('h'=>array('i'=>$hItem),'r'=>array('i'=>$rItem));
        }       
        return $result;
    }
}
